﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace IFRS
{
    /// <summary>
    /// Database manupulation class.
    /// </summary>
    public class Database
    {
        /// <summary>
        /// Initiate connection to NSO database.
        /// </summary>
        /// <returns>Oracle Connection</returns>
        public static SqlConnection IFRSConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["IFRS"].ConnectionString);
        }

        /// <summary>
        /// Get data table as of query text.
        /// </summary>
        /// <param name="Con">Sql Connection to IFRS database</param>
        /// <param name="CmdType">Type of command (Text or Stored Procedure)</param>
        /// <param name="CmdText">Command text</param>
        /// <param name="Parameters">List of parameters (optional)</param>
        /// <returns>Data table</returns>
        public static DataTable GetDataTable(SqlConnection Con, CommandType CmdType, string CmdText, List<SqlParameter> Parameters = null)
        {
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Con;
            Cmd.CommandType = CmdType;
            Cmd.CommandText = CmdText;
            if (Parameters != null)
            {
                foreach (SqlParameter param in Parameters)
                {
                    Cmd.Parameters.Add(param);
                }
            }
            SqlDataAdapter da = new SqlDataAdapter(Cmd);
            DataTable dt = new DataTable();

            try
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
                Con.Open();
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
                Con.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// Execute the given command text
        /// </summary>
        /// <param name="Con">Sql Connection to IFRS database</param>
        /// <param name="CmdType">Type of command (Text or Stored Procedure)</param>
        /// <param name="CmdText">Command text</param>
        /// <param name="Parameters">List of parameters (optional)</param>
        /// <returns name="Sucess">1 = Success, 0 = Error</returns>
        public static int ExecuteCommand(SqlConnection Con, CommandType CmdType, string CmdText, List<SqlParameter> Parameters = null)
        {
            SqlTransaction trn;
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Con;
            Cmd.CommandType = CmdType;
            Cmd.CommandText = CmdText;
            int Success = 0;

            try
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
                Con.Open();
                trn = Con.BeginTransaction();
                Cmd.Transaction = trn;
                if (Parameters != null)
                {
                    foreach (SqlParameter param in Parameters)
                    {
                        Cmd.Parameters.Add(param);
                    }
                }
                Cmd.ExecuteNonQuery();
                trn.Commit();
                Success = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return Success;
        }

        /// <summary>
        /// Initiate an Sql Parameter.
        /// </summary>
        /// <param name="Name">Parameter name</param>
        /// <param name="Val">Value of parameter</param>
        /// <returns>New Sql Parameter</returns>
        public static SqlParameter SetParameter(string Name, Object Value)
        {
            SqlParameter param = new SqlParameter(Name, Value);
            return param;
        }

        /// <summary>
        /// Initiate an Sql Parameter.
        /// </summary>
        /// <param name="Name">Parameter name</param>
        /// <param name="Val">Value of the parameter</param>
        /// <param name="DbType">Data type of the parameter</param>
        /// <param name="Size">Size or length of the parameter</param>
        /// <returns>New Sql Parameter</returns>
        public static SqlParameter SetParameter(string Name, Object Value, SqlDbType DbType, Int32 Size = 0)
        {
            SqlParameter param = new SqlParameter(Name, Value);
            param.SqlDbType = DbType;
            if (Size > 0)
            {
                param.Size = Size;
            }
            return param;
        }
    }
}